"""
商品评分数据库管理器
"""
from typing import List, Optional, Dict, Any
from datetime import datetime
from sqlalchemy import text
from sqlalchemy import text
from .sqlalchemy_database import SQLAlchemyDatabaseManager
from com.utils.logger import get_logger

logger = get_logger("GoodsScoreManager")

class GoodsScoreManager:
    """商品评分数据库管理器"""
    
    def __init__(self):
        """初始化管理器"""
        self.db = SQLAlchemyDatabaseManager()

    def save_score_result(self, result: 'ScoreResult') -> bool:
        """
        保存评分结果到数据库
        """
        try:
            sql = """
            INSERT OR REPLACE INTO goods_score_analysis (
                goods_id,
                price_stability_score,
                profit_potential_score,
                trading_frequency_score,
                price_trend_score,
                time_activity_score,
                historical_success_score,
                total_score,
                grade,
                stability_cv_score,
                stability_volatility_score,
                profit_rate_score,
                profit_frequency_score,
                profit_time_weighted_score,
                frequency_activity_score,
                frequency_selling_change_score,
                frequency_price_fluctuation_score,
                trend_direction_score,
                trend_range_score,
                trend_stability_score,
                last_7d_trade_count,
                last_7d_avg_price,
                price_variance,
                success_rate,
                create_at,
                update_at
            ) VALUES (
                :goods_id,
                :price_stability_score,
                :profit_potential_score,
                :trading_frequency_score,
                :price_trend_score,
                :time_activity_score,
                :historical_success_score,
                :total_score,
                :grade,
                :stability_cv_score,
                :stability_volatility_score,
                :profit_rate_score,
                :profit_frequency_score,
                :profit_time_weighted_score,
                :frequency_activity_score,
                :frequency_selling_change_score,
                :frequency_price_fluctuation_score,
                :trend_direction_score,
                :trend_range_score,
                :trend_stability_score,
                :last_7d_trade_count,
                :last_7d_avg_price,
                :price_variance,
                :success_rate,
                :create_at,
                :update_at
            )
            """
            
            params = {
                'goods_id': result.goods_id,
                'price_stability_score': result.price_stability_score,
                'profit_potential_score': result.profit_potential_score,
                'trading_frequency_score': result.trading_frequency_score,
                'price_trend_score': result.price_trend_score,
                'time_activity_score': result.time_activity_score,
                'historical_success_score': result.historical_success_score,
                'total_score': result.total_score,
                'grade': result.grade,
                'stability_cv_score': result.stability_cv_score,
                'stability_volatility_score': result.stability_volatility_score,
                'profit_rate_score': result.profit_rate_score,
                'profit_frequency_score': result.profit_frequency_score,
                'profit_time_weighted_score': result.profit_time_weighted_score,
                'frequency_activity_score': result.frequency_activity_score,
                'frequency_selling_change_score': result.frequency_selling_change_score,
                'frequency_price_fluctuation_score': result.frequency_price_fluctuation_score,
                'trend_direction_score': result.trend_direction_score,
                'trend_range_score': result.trend_range_score,
                'trend_stability_score': result.trend_stability_score,
                'last_7d_trade_count': result.last_7d_trade_count,
                'last_7d_avg_price': result.last_7d_avg_price,
                'price_variance': result.price_variance,
                'success_rate': result.success_rate,
                'create_at': datetime.now(),
                'update_at': datetime.now()
            }
            
            self.db.execute(sql, params)
            return True
            
        except Exception as e:
            logger.error(f"保存评分结果时出错: {str(e)}", exc_info=True)
            return False

    def batch_save_score_results(self, results: List['ScoreResult']) -> bool:
        """
        批量保存评分结果到数据库
        """
        try:
            sql = """
            INSERT OR REPLACE INTO goods_score_analysis (
                goods_id,
                price_stability_score,
                profit_potential_score,
                trading_frequency_score,
                price_trend_score,
                time_activity_score,
                historical_success_score,
                total_score,
                grade,
                stability_cv_score,
                stability_volatility_score,
                profit_rate_score,
                profit_frequency_score,
                profit_time_weighted_score,
                frequency_activity_score,
                frequency_selling_change_score,
                frequency_price_fluctuation_score,
                trend_direction_score,
                trend_range_score,
                trend_stability_score,
                last_7d_trade_count,
                last_7d_avg_price,
                price_variance,
                success_rate,
                create_at,
                update_at
            ) VALUES (
                :goods_id,
                :price_stability_score,
                :profit_potential_score,
                :trading_frequency_score,
                :price_trend_score,
                :time_activity_score,
                :historical_success_score,
                :total_score,
                :grade,
                :stability_cv_score,
                :stability_volatility_score,
                :profit_rate_score,
                :profit_frequency_score,
                :profit_time_weighted_score,
                :frequency_activity_score,
                :frequency_selling_change_score,
                :frequency_price_fluctuation_score,
                :trend_direction_score,
                :trend_range_score,
                :trend_stability_score,
                :last_7d_trade_count,
                :last_7d_avg_price,
                :price_variance,
                :success_rate,
                :create_at,
                :update_at
            )
            """
            
            session = self.db.get_session()
            try:
                for result in results:
                    params = {
                        'goods_id': result.goods_id,
                        'price_stability_score': result.price_stability_score,
                        'profit_potential_score': result.profit_potential_score,
                        'trading_frequency_score': result.trading_frequency_score,
                        'price_trend_score': result.price_trend_score,
                        'time_activity_score': result.time_activity_score,
                        'historical_success_score': result.historical_success_score,
                        'total_score': result.total_score,
                        'grade': result.grade,
                        'stability_cv_score': result.stability_cv_score,
                        'stability_volatility_score': result.stability_volatility_score,
                        'profit_rate_score': result.profit_rate_score,
                        'profit_frequency_score': result.profit_frequency_score,
                        'profit_time_weighted_score': result.profit_time_weighted_score,
                        'frequency_activity_score': result.frequency_activity_score,
                        'frequency_selling_change_score': result.frequency_selling_change_score,
                        'frequency_price_fluctuation_score': result.frequency_price_fluctuation_score,
                        'trend_direction_score': result.trend_direction_score,
                        'trend_range_score': result.trend_range_score,
                        'trend_stability_score': result.trend_stability_score,
                        'last_7d_trade_count': result.last_7d_trade_count,
                        'last_7d_avg_price': result.last_7d_avg_price,
                        'price_variance': result.price_variance,
                        'success_rate': result.success_rate,
                        'create_at': datetime.now(),
                        'update_at': datetime.now()
                    }
                    session.execute(text(sql), params)
                session.commit()
                return True
            except Exception as e:
                session.rollback()
                raise e
            finally:
                session.close()
            
        except Exception as e:
            logger.error(f"批量保存评分结果时出错: {str(e)}", exc_info=True)
            return False

    def get_score_result(self, goods_id: int) -> Optional[Dict[str, Any]]:
        """
        获取商品的评分结果
        """
        try:
            sql = """
            SELECT * FROM goods_score_analysis
            WHERE goods_id = :goods_id
            """
            
            results = self.db.execute(sql, {'goods_id': goods_id})
            return dict(results[0]) if results else None
            
        except Exception as e:
            logger.error(f"获取商品 {goods_id} 的评分结果时出错: {str(e)}", exc_info=True)
            return None

    def get_goods_by_grade(self, grade: str) -> List[Dict[str, Any]]:
        """
        获取指定等级的所有商品
        """
        try:
            sql = """
            SELECT * FROM goods_score_analysis
            WHERE grade = :grade
            ORDER BY total_score DESC
            """
            
            session = self.db.get_session()
            try:
                result = session.execute(text(sql), {'grade': grade})
                return [dict(row._mapping) for row in result]
            finally:
                session.close()
            
        except Exception as e:
            logger.error(f"获取等级 {grade} 的商品时出错: {str(e)}", exc_info=True)
            return []

    def get_outdated_goods(self, hours: int = 1) -> List[int]:
        """
        获取需要更新评分的商品ID列表
        """
        try:
            sql = """
            SELECT goods_id FROM goods_score_analysis
            WHERE updated_at < datetime('now', :hours || ' hours')
            """
            
            results = self.db.execute(sql, {'hours': f"-{hours}"})
            return [row[0] for row in results]
            
        except Exception as e:
            logger.error(f"获取过期商品列表时出错: {str(e)}", exc_info=True)
            return []
